package com.ayuku.commons;

import jxl.write.Boolean;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * 
 * @author putu kusuma <putukn@gmail.com>
 *
 */
public class UtilExportToExcel {

	/**
	 * Writes out a sheet containing the various numerical formats
	 * 
	 * @param s
	 */
	public static void writeNumberFormatSheet(WritableSheet s)
			throws WriteException {
		WritableCellFormat wrappedText = new WritableCellFormat(
				WritableWorkbook.ARIAL_10_PT);
		wrappedText.setWrap(true);

		s.setColumnView(0, 20);
		s.setColumnView(4, 20);
		s.setColumnView(5, 20);
		s.setColumnView(6, 20);

		// Floats
		Label l = new Label(0, 0, "+/- Pi - default format", wrappedText);
		s.addCell(l);

		Number n = new Number(1, 0, 3.1415926535);
		s.addCell(n);

		n = new Number(2, 0, -3.1415926535);
		s.addCell(n);

		l = new Label(0, 1, "+/- Pi - integer format", wrappedText);
		s.addCell(l);

		WritableCellFormat cf1 = new WritableCellFormat(NumberFormats.INTEGER);
		n = new Number(1, 1, 3.1415926535, cf1);
		s.addCell(n);

		n = new Number(2, 1, -3.1415926535, cf1);
		s.addCell(n);

		l = new Label(0, 2, "+/- Pi - float 2dps", wrappedText);
		s.addCell(l);

		WritableCellFormat cf2 = new WritableCellFormat(NumberFormats.FLOAT);
		n = new Number(1, 2, 3.1415926535, cf2);
		s.addCell(n);

		n = new Number(2, 2, -3.1415926535, cf2);
		s.addCell(n);

		l = new Label(0, 3, "+/- Pi - custom 3dps", wrappedText);
		s.addCell(l);

		NumberFormat dp3 = new NumberFormat("#.###");
		WritableCellFormat dp3cell = new WritableCellFormat(dp3);
		n = new Number(1, 3, 3.1415926535, dp3cell);
		s.addCell(n);

		n = new Number(2, 3, -3.1415926535, dp3cell);
		s.addCell(n);

		l = new Label(0, 4, "+/- Pi - custom &3.14", wrappedText);
		s.addCell(l);

		NumberFormat pounddp2 = new NumberFormat("&#.00");
		WritableCellFormat pounddp2cell = new WritableCellFormat(pounddp2);
		n = new Number(1, 4, 3.1415926535, pounddp2cell);
		s.addCell(n);

		n = new Number(2, 4, -3.1415926535, pounddp2cell);
		s.addCell(n);

		l = new Label(0, 5, "+/- Pi - custom Text #.### Text", wrappedText);
		s.addCell(l);

		NumberFormat textdp4 = new NumberFormat("Text#.####Text");
		WritableCellFormat textdp4cell = new WritableCellFormat(textdp4);
		n = new Number(1, 5, 3.1415926535, textdp4cell);
		s.addCell(n);

		n = new Number(2, 5, -3.1415926535, textdp4cell);
		s.addCell(n);

		// Integers
		l = new Label(4, 0, "+/- Bilko default format");
		s.addCell(l);
		n = new Number(5, 0, 15042699);
		s.addCell(n);
		n = new Number(6, 0, -15042699);
		s.addCell(n);

		l = new Label(4, 1, "+/- Bilko float format");
		s.addCell(l);
		WritableCellFormat cfi1 = new WritableCellFormat(NumberFormats.FLOAT);
		n = new Number(5, 1, 15042699, cfi1);
		s.addCell(n);
		n = new Number(6, 1, -15042699, cfi1);
		s.addCell(n);

		l = new Label(4, 2, "+/- Thousands separator");
		s.addCell(l);
		WritableCellFormat cfi2 = new WritableCellFormat(
				NumberFormats.THOUSANDS_INTEGER);
		n = new Number(5, 2, 15042699, cfi2);
		s.addCell(n);
		n = new Number(6, 2, -15042699, cfi2);
		s.addCell(n);

		l = new Label(4, 3, "+/- Accounting red - added 0.01");
		s.addCell(l);
		WritableCellFormat cfi3 = new WritableCellFormat(
				NumberFormats.ACCOUNTING_RED_FLOAT);
		n = new Number(5, 3, 15042699.01, cfi3);
		s.addCell(n);
		n = new Number(6, 3, -15042699.01, cfi3);
		s.addCell(n);

		l = new Label(4, 4, "+/- Percent");
		s.addCell(l);
		WritableCellFormat cfi4 = new WritableCellFormat(
				NumberFormats.PERCENT_INTEGER);
		n = new Number(5, 4, 15042699, cfi4);
		s.addCell(n);
		n = new Number(6, 4, -15042699, cfi4);
		s.addCell(n);

		l = new Label(4, 5, "+/- Exponential - 2dps");
		s.addCell(l);
		WritableCellFormat cfi5 = new WritableCellFormat(
				NumberFormats.EXPONENTIAL);
		n = new Number(5, 5, 15042699, cfi5);
		s.addCell(n);
		n = new Number(6, 5, -15042699, cfi5);
		s.addCell(n);

		l = new Label(4, 6, "+/- Custom exponentional - 3dps", wrappedText);
		s.addCell(l);
		NumberFormat edp3 = new NumberFormat("0.000E0");
		WritableCellFormat edp3Cell = new WritableCellFormat(edp3);
		n = new Number(5, 6, 15042699, edp3Cell);
		s.addCell(n);
		n = new Number(6, 6, -15042699, edp3Cell);
		s.addCell(n);

		l = new Label(4, 7, "Custom neg brackets", wrappedText);
		s.addCell(l);
		NumberFormat negbracks = new NumberFormat("#,##0;(#,##0)");
		WritableCellFormat negbrackscell = new WritableCellFormat(negbracks);
		n = new Number(5, 7, 15042699, negbrackscell);
		s.addCell(n);
		n = new Number(6, 7, -15042699, negbrackscell);
		s.addCell(n);

		l = new Label(4, 8, "Custom neg brackets 2", wrappedText);
		s.addCell(l);
		NumberFormat negbracks2 = new NumberFormat("#,##0;(#,##0)a");
		WritableCellFormat negbrackscell2 = new WritableCellFormat(negbracks2);
		n = new Number(5, 8, 15042699, negbrackscell2);
		s.addCell(n);
		n = new Number(6, 8, -15042699, negbrackscell2);
		s.addCell(n);

		l = new Label(4, 9, "Custom percent", wrappedText);
		s.addCell(l);
		NumberFormat cuspercent = new NumberFormat("0.0%");
		WritableCellFormat cuspercentf = new WritableCellFormat(cuspercent);
		n = new Number(5, 9, 3.14159265, cuspercentf);
		s.addCell(n);

		// Booleans
		l = new Label(0, 10, "Boolean - TRUE");
		s.addCell(l);
		Boolean b = new Boolean(1, 10, true);
		s.addCell(b);

		l = new Label(0, 11, "Boolean - FALSE");
		s.addCell(l);
		b = new Boolean(1, 11, false);
		s.addCell(b);

		l = new Label(0, 12, "A hidden cell->");
		s.addCell(l);
		n = new Number(1, 12, 17, WritableWorkbook.HIDDEN_STYLE);
		s.addCell(n);

		// Currencies
		l = new Label(4, 19, "Currency formats");
		s.addCell(l);

		l = new Label(4, 21, "UK Pound");
		s.addCell(l);
		NumberFormat poundCurrency = new NumberFormat(
				NumberFormat.CURRENCY_POUND + " #,###.00",
				NumberFormat.COMPLEX_FORMAT);
		WritableCellFormat poundFormat = new WritableCellFormat(poundCurrency);
		n = new Number(5, 21, 12345, poundFormat);
		s.addCell(n);

		l = new Label(4, 22, "Euro 1");
		s.addCell(l);
		NumberFormat euroPrefixCurrency = new NumberFormat(
				NumberFormat.CURRENCY_EURO_PREFIX + " #,###.00",
				NumberFormat.COMPLEX_FORMAT);
		WritableCellFormat euroPrefixFormat = new WritableCellFormat(
				euroPrefixCurrency);
		n = new Number(5, 22, 12345, euroPrefixFormat);
		s.addCell(n);

		l = new Label(4, 23, "Euro 2");
		s.addCell(l);
		NumberFormat euroSuffixCurrency = new NumberFormat("#,###.00"
				+ NumberFormat.CURRENCY_EURO_SUFFIX,
				NumberFormat.COMPLEX_FORMAT);
		WritableCellFormat euroSuffixFormat = new WritableCellFormat(
				euroSuffixCurrency);
		n = new Number(5, 23, 12345, euroSuffixFormat);
		s.addCell(n);

		l = new Label(4, 24, "Dollar");
		s.addCell(l);
		NumberFormat dollarCurrency = new NumberFormat(
				NumberFormat.CURRENCY_DOLLAR + " #,###.00",
				NumberFormat.COMPLEX_FORMAT);
		WritableCellFormat dollarFormat = new WritableCellFormat(dollarCurrency);
		n = new Number(5, 24, 12345, dollarFormat);
		s.addCell(n);

		l = new Label(4, 25, "Japanese Yen");
		s.addCell(l);
		NumberFormat japaneseYenCurrency = new NumberFormat(
				NumberFormat.CURRENCY_JAPANESE_YEN + " #,###.00",
				NumberFormat.COMPLEX_FORMAT);
		WritableCellFormat japaneseYenFormat = new WritableCellFormat(
				japaneseYenCurrency);
		n = new Number(5, 25, 12345, japaneseYenFormat);
		s.addCell(n);

		l = new Label(4, 30, "Fraction formats");
		s.addCell(l);

		l = new Label(4, 32, "One digit fraction format", wrappedText);
		s.addCell(l);

		WritableCellFormat fraction1digitformat = new WritableCellFormat(
				NumberFormats.FRACTION_ONE_DIGIT);
		n = new Number(5, 32, 3.18279, fraction1digitformat);
		s.addCell(n);

		l = new Label(4, 33, "Two digit fraction format", wrappedText);
		s.addCell(l);

		WritableCellFormat fraction2digitformat = new WritableCellFormat(
				NumberFormats.FRACTION_TWO_DIGITS);
		n = new Number(5, 33, 3.18279, fraction2digitformat);
		s.addCell(n);

		l = new Label(4, 34, "Three digit fraction format (improper)",
				wrappedText);
		s.addCell(l);

		NumberFormat fraction3digit1 = new NumberFormat(
				NumberFormat.FRACTION_THREE_DIGITS, NumberFormat.COMPLEX_FORMAT);
		WritableCellFormat fraction3digitformat1 = new WritableCellFormat(
				fraction3digit1);
		n = new Number(5, 34, 3.18927, fraction3digitformat1);
		s.addCell(n);

		l = new Label(4, 35, "Three digit fraction format (proper)",
				wrappedText);
		s.addCell(l);

		NumberFormat fraction3digit2 = new NumberFormat("# "
				+ NumberFormat.FRACTION_THREE_DIGITS,
				NumberFormat.COMPLEX_FORMAT);
		WritableCellFormat fraction3digitformat2 = new WritableCellFormat(
				fraction3digit2);
		n = new Number(5, 35, 3.18927, fraction3digitformat2);
		s.addCell(n);

		// Lots of numbers
		for (int row = 0; row < 100; row++) {
			for (int col = 8; col < 108; col++) {
				n = new Number(col, row, col + row);
				s.addCell(n);
			}
		}

		// Lots of numbers
		for (int row = 101; row < 3000; row++) {
			for (int col = 0; col < 25; col++) {
				n = new Number(col, row, col + row);
				s.addCell(n);
			}
		}
	}

}
